const express = require("express");
const router = express.Router();
const { db, genid } = require("../db/DBUtils");


// 添加博客
router.post("/_token/add", async (req, res) => {
    let { title, categoryId, content } = req.body;
    let id = genid.NextId();
    let create_time = new Date().getTime();

    const insert_sql = "INSERT INTO `blog` (`id`,`title`,`category_id`,`content`,`create_time`) VALUES (?,?,?,?,?)";
    let params = [id, title, categoryId, content, create_time];

    let { err, rows } = await db.async.run(insert_sql, params);

    if (err == null) {
        res.send({
            code: 200,
            msg:"添加成功"
        })
    } else {
        res.send({
            code: 500,
            msg:"添加失败"
        })
    }
})

// 修改博客
router.put("/_token/update", async (req, res) => {
    let { id, title, categoryId, content } = req.body;
    let create_time = new Date().getTime();

    const update_sql = "UPDATE `blog` SET `title` = ?, `content` = ?, `category_id` = ? WHERE `id` = ?";
    let params = [title, content, categoryId, id];

    let { err, rows } = await db.async.run(update_sql, params);

    if (err == null) {
        res.send({
            code: 200,
            msg:"修改成功"
        })
    } else {
        res.send({
            code: 500,
            msg:"修改失败"
        })
    }
})

// 删除接口
// /blog/_token/delete?id=xxx
router.delete("/_token/delete", async (req, res) => {
    let id = req.query.id;
    const delete_sql = "DELETE FROM `blog` WHERE `id` = ?";
    let { err, rows } = await db.async.run(delete_sql, [id]);

    if (err == null) {
        res.send({
            code: 200,
            msg:"删除成功"
        })
    } else {
        res.send({
            code: 500,
            msg:"删除失败"
        })
    }
})

// 查询博客
router.get("/search", async (req, res) => {
    /**
     * keyword---关键字
     * categoryId---分类编号
     * 
     * 分页：
     * page---页码
     * pageSize---分页大小
     */

    let { keyword, categoryId, page, pageSize } = req.query;

    // 前端page不传，则默认为1，若传，则为传的值
    page = page == null ? 1 : page;

    // 前端pageSize不传，则默认为10，若传，则为传的值
    pageSize = pageSize == null ? 10 : pageSize;

    // 前端categoryId不传，则默认为0，若传，则为传的值
    categoryId = categoryId == null ? 0 : categoryId;

    // 前端keyword不传，则默认为空，若传，则为传的值
    keyword = keyword == null ? "" : keyword;

    let params = [];
    let whereSqls = [];
    if (categoryId != 0) {
        whereSqls.push(" `category_id` = ? ");
        params.push(categoryId);
    }

    if (keyword != "") {
        whereSqls.push(" (`title` LIKE ? OR `content` LIKE ?) ");
        params.push("%" + keyword + "%");
        params.push("%" + keyword + "%");
    }

    let whereSqlStr = "";
    if (whereSqls.length > 0) {
        whereSqlStr = " WHERE " + whereSqls.join(" AND ");
    }
    // [" `category_id` = ? ", " `title` LIKE ? OR `content` LIKE ? "]
    // WHERE `category_id` = ? AND `title` LIKE ? OR `content` LIKE ?

    // 查询分页数据
    let searchSql = " SELECT `id`,`category_id`,`create_time`,`title`,substr(`content`,0,50) AS `content` FROM `blog` " + whereSqlStr + " ORDER BY `create_time` DESC LIMIT ?,? ";
    let searchSqlParams = params.concat([(page - 1) * pageSize, pageSize]);

    // 查询数据总数
    let searchCountSql = " SELECT count(*) as `count` FROM `blog` " + whereSqlStr;
    let searchCountParams = params;

    // 分页数据
    let searchResult = await db.async.all(searchSql, searchSqlParams);
    // 数据总数
    let countResult = await db.async.all(searchCountSql, searchCountParams);

    console.log(countResult);

    if (searchResult.err == null && countResult.err == null) {
        res.send({
            code: 200,
            msg: "查询成功",
            data: {
                keyword,
                categoryId,
                page,
                pageSize,
                rows: searchResult.rows,
                count: countResult.rows[0].count
            }
        });
    } else {
        res.send({
            code: 500,
            msg: "查询失败"
        })
    }  
})

router.get("/detail", async (req, res) => {

    let { id } = req.query
    let detail_sql = "SELECT * FROM `blog` WHERE `id` = ? "
    let { err, rows } = await db.async.all(detail_sql, [id]) ;

    if (err == null) {
        res.send({
            code: 200,
            msg: "获取成功",
            rows
        })
    } else {
        res.send({
            code: 500,
            msg: "获取失败"
        })
    }

})

module.exports = router;